Re: User privileges in web database applications

I'm coming in a bit late to this conversation, so forgive me if I've missed
something. Isn't this problem the reason that connection pools were created?

In a connection pool, connections are only associated with a particular
user for the duration of a transaction. Once the transaction is complete,
the connection goes back to the pool.

Pool sizes are usually configurable, and the number of actual connections
in the pool can be less than the configured amount. The pool is responsible
for allocating blocks of connection - up to MAX - as necessary.

The pool is also responsible for guaranteeing that a connection is valid
when it's requested from the pool. This may involve periodic 'pinging' of
unused connections, or simply testing, and recreating if nessecary, a
connection before it's reserved for use.

I'm pretty sure there is a pgpool project somewhere to provide exactly this
service.

Tim
___
Sent with SnapperMail
www.snappermail.com

...... Original Message .......
On Thu, 29 Jun 2006 10:19:34 -0400 "John DeSoi" <desoi@pgedit.com> wrote:
>
>On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote:
>
>> The benefit of this solution is that
>> I avoid reconnecting to the database on each request, having instead a
>> persistent connection as user "postgres".
>
>But it is also a resource liability. How do you know if the user will
>make another request or when they are "finished" using your site? You
>can certainly time out the connection but for a busy site you will
>potentially be holding open a large number of connections that will
>never be used again.
>
>I agree with your reasons for wanting to do this, but it may not be
>feasible for a busy web site.